package org.springblade.common.utils;

import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @Description excel三级联动工具类
 * @Author ysq
 * @Date 2023/8/15
 **/
public class PoiSelectExcelUtil {

	/**
	 * 计算
	 *
	 * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
	 * @param rowId    第几行
	 * @param colCount 一共多少列
	 * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
	 */
	private static String getRange(int offset, int rowId, int colCount) {
		char start = (char) ('A' + offset);
		if (colCount <= 25) {
			char end = (char) (start + colCount - 1);
			return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
		} else {
			char endPrefix = 'A';
			char endSuffix = 'A';
			if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
				if ((colCount - 25) % 26 == 0) {// 边界值
					endSuffix = (char) ('A' + 25);
				} else {
					endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
				}
			} else {// 51以上
				if ((colCount - 25) % 26 == 0) {
					endSuffix = (char) ('A' + 25);
					endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
				} else {
					endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
					endPrefix = (char) (endPrefix + (colCount - 25) / 26);
				}
			}
			return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
		}
	}

	public static void writeData(XSSFWorkbook xssfWorkBook, XSSFSheet mapSheet, List<String> provinceList, Map<String, List<String>> siteMap) {
		//循环将父数据写入siteSheet的第1行中
		int siteRowId = 0;
		XSSFRow provinceRow = mapSheet.createRow(siteRowId++);
		provinceRow.createCell(0).setCellValue("父列表");
		for (int i = 0; i < provinceList.size(); i++) {
			provinceRow.createCell(i + 1).setCellValue(provinceList.get(i));
		}
		// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
		Iterator<String> keyIterator = siteMap.keySet().iterator();
		while (keyIterator.hasNext()) {
			String key = keyIterator.next();
			List<String> son = siteMap.get(key);
			XSSFRow siteRow = mapSheet.createRow(siteRowId++);
			siteRow.createCell(0).setCellValue(key);
			for (int i = 0; i < son.size(); i++) {
				siteRow.createCell(i + 1).setCellValue(son.get(i));
			}

			// 添加名称管理器
			String range = getRange(1, siteRowId, son.size());
			Name name = xssfWorkBook.createName();
			name.setNameName(key);
			String formula = mapSheet.getSheetName() + "!" + range;
			name.setRefersToFormula(formula);
		}
	}

	public static void setDataValid(XSSFWorkbook XSSFWorkBook, XSSFSheet mainSheet, List<String> provinceList) {
		//设置一级下拉
		XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) mainSheet);
		String[] dataArray = provinceList.toArray(new String[0]);
		XSSFSheet hidden = XSSFWorkBook.createSheet("hidden");
		XSSFCell cell = null;
		for (int i = 0, length = dataArray.length; i < length; i++) {
			String name = dataArray[i];
			XSSFRow row = hidden.createRow(i);
			cell = row.createCell(0);
			cell.setCellValue(name);
		}

		String strFormula = "hidden!$A$1:$A$"+ dataArray.length;
		XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);

		// 四个参数分别是：起始行、终止行、起始列、终止列.
		// 1 (一级下拉框代表从excel第1+1行开始) 10(一级下拉框代表从excel第1+10行结束) 1(代表第几列开始，0是第一列，1是第二列) 1(代表第几列结束，0是第一列，1是第二列)
		CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(2, 10, 1, 1);
		DataValidation provinceDataValidation = dvHelper.createValidation(constraint, provinceRangeAddressList);
		provinceDataValidation.createErrorBox("error", "请选择正确数据");
		provinceDataValidation.setShowErrorBox(true);
		//设置sheet是否隐藏  true:隐藏/false:显示
		XSSFWorkBook.setSheetHidden(XSSFWorkBook.getSheetIndex(hidden), true);
		mainSheet.addValidationData(provinceDataValidation);

		// 设置二级、三级下拉 设置1000行
		for (int i = 0; i <= 1000; i++) {
			// "B"是指父类所在的列，i+1初始值为1代表从第2行开始，2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么最后一个参数就3
			setDataValidation('B', mainSheet, i + 2, 2);
		}
	}

	/**
	 * 设置有效性
	 *
	 * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
	 * @param sheet
	 * @param rowNum 行数
	 * @param colNum 列数
	 */
	public static void setDataValidation(char offset, XSSFSheet sheet, int rowNum, int colNum) {
		XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
		DataValidation dataValidationList1;
		DataValidation dataValidationList2;
		dataValidationList1 = getDataValidationByFormula("INDIRECT($" + offset + (rowNum + 1) + ")", rowNum, colNum, dvHelper);
		dataValidationList2 = getDataValidationByFormula("INDIRECT($" + (char) (offset + 1) + (rowNum + 1) + ")", rowNum, colNum + 1, dvHelper);
		sheet.addValidationData(dataValidationList1);
		sheet.addValidationData(dataValidationList2);
	}

	private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, XSSFDataValidationHelper dvHelper) {
		DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
		CellRangeAddressList regions = new CellRangeAddressList(naturalRowIndex, 65535, naturalColumnIndex, naturalColumnIndex);
		XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
		data_validation_list.setEmptyCellAllowed(false);
		if (data_validation_list instanceof XSSFDataValidation) {
			data_validation_list.setShowErrorBox(true);
		}
		// 设置输入信息提示信息
//		data_validation_list.createPromptBox("提示", "请使用下拉方式选择合适的值！");
		return data_validation_list;
	}
}
